CREATE OR REPLACE PACKAGE pkg_manip
IS
   
   --RETURNS Y OR N  
   FUNCTION isnumeric (infield IN VARCHAR2)                                                                                                                                                                                
   RETURN VARCHAR2;
   --THIS PROCEDURE WILL FORMAT A STRING TO BE UPPER 
   --AND TRIM 
   FUNCTION trimUpper( aString IN VARCHAR2 )
   RETURN VARCHAR2;
   
   --returns date in MM/DD/YYYY HH:MI:SS Format   
   FUNCTION dateTime( aDate IN DATE )
   RETURN VARCHAR2;
   
   --return date in HH:MI am, MM/DD/YYYY format  
   FUNCTION timeDate( aDate IN DATE )
   RETURN VARCHAR2;
   
   --return date in MM/DD/YYYY Format
   FUNCTION dt( aDate IN DATE )
   RETURN VARCHAR2;
   
   --return NUMBER formated to $
   FUNCTION numToDollars
      ( aNumber IN NUMBER
      , withDollar IN VARCHAR DEFAULT 'Y' )
   RETURN VARCHAR2;
   
   /**************************************************************************
   FUNCTION f_make_date
    Summary: Validate and convert date input of most any format.
         "date manager conversion". Accepts
         a character string and returns a fully-parsed and validated
         date. If the string does not specify a valid date, the function
         returns NULL.

    Handle the short-cut logic before checking for specific date formats.
    Supported short-cuts include:
       EW - end of week = Friday of the current week  
       BW - beginning of week = Saturday of the current week 
       EM - end of month = last day of the month eg. 1/31/2007 or 2/28/2007 
       BM - beginning of month = first day of the month  
       TODAY - today's truncated date 
       YESTERDAY - yesterday's truncated date 
       TOMORROW - tomorrow's truncated date 
   **************************************************************************/    
   FUNCTION f_make_date (value_in IN VARCHAR2)
   RETURN DATE;
   
   /***********************************************************************
   FUNCTION f_make_cc_date
   Summary: Attempts to convert strings in the MMYY format 1st, if it can't
            the value_in is passed to the regular f_make_date
   ************************************************************************/
   FUNCTION f_make_cc_date (value_in IN VARCHAR2)
   RETURN DATE;
   
   
   /**********************************************************************
   FUNCTION f_no_cr
   Summary: Removes carriage return, then returns edited string.
   **********************************************************************/
   FUNCTION f_no_cr( value_in IN VARCHAR2 )
   RETURN VARCHAR2;
   
   /**********************************************************************
   FUNCTION f_chop
   Summary: Returns only the LHS of the string passed in 
   Example: f_chop( 'My_test', '_' ) will return My
   On Error: string_in is returned unedited    
   ***********************************************************************/
   FUNCTION f_chop( string_in IN VARCHAR2, chop_string IN VARCHAR2 )
   RETURN VARCHAR2;
   
   /**********************************************************************
   FUNCTION f_rchop
   Summary: Returns only the RHS of the string passed in 
   Example: f_rchop( 'My_test', '_' ) will return test
   On Error: string_in is returned unedited 
   ***********************************************************************/
   FUNCTION f_rchop( string_in IN VARCHAR2, chop_string IN VARCHAR2 )
   RETURN VARCHAR2; 

   /**********************************************************************   
   FUNCTION f_trail_off 
   Summary: If the string_in is greater than the char_to_start_trailing
            then the trailing_chars are appended to the string_in at
            the char_to_start_trailing.  Otherwise, the string_in is returned 
   Example: f_trail_off( 'My Test String', 7, '...' ) will return "MyTest..." 
   On Error: string is returned unedited  
   **********************************************************************/   
   FUNCTION f_trail_off
      ( string_in IN VARCHAR2
      , char_to_start_trailing IN INTEGER
      , trailing_chars IN VARCHAR2 )
   RETURN VARCHAR2;      
   
  /**********************************************************************   
  FUNCTION f_wrap 
  Summary: the_string is wrapped with the wrapper, if the rhs_wrapper is 
           also supplied, then the wrapper will only be applied to the lhs of 
           the string  
  Example: f_wrap( 'My Test String', '*' ) will return *My Test String* 
  On Error: string is returned unedited  
  **********************************************************************/ 
  FUNCTION f_wrap
    ( the_string IN VARCHAR2
    , wrapper IN VARCHAR2 )
  RETURN VARCHAR2;

  /**********************************************************************   
  FUNCTION f_wrap 
  Summary: the_string is wrapped with the wrapper, if the rhs_wrapper is 
           also supplied, then the wrapper will only be applied to the lhs of 
           the string  
  Example: f_wrap( 'My Test String', '*' ) will return *My Test String* 
  On Error: string is returned unedited  
  **********************************************************************/ 
  FUNCTION f_wrap
    ( the_string IN VARCHAR2
    , lhs_wrapper IN VARCHAR2
    , rhs_wrapper IN VARCHAR2 )
  RETURN VARCHAR2;
  
  /************************************************************************
  FUNCTION f_strip 
  Summary: Removes the strip string from the beginning and the end of the_string 
  Example: f_strip( '|string|', '|' ) RETURNS string '
  *************************************************************************/
  FUNCTION f_strip
    ( the_string IN VARCHAR2
    , the_strip_string IN VARCHAR2 )
  RETURN VARCHAR2;
  
  /************************************************************************
  FUNCTION f_delimit_me
  Summary: Turns an array into a delimited string using the_delimiter as the delimiter 
  Example: my_array[ 'red', 'blue', 'green' ] 
            f_delimit_me( my_array, '?' ) => red?blue?green?  
  *************************************************************************/  
  FUNCTION f_delimit_me
    ( the_array IN owa_util.vc_arr
    , the_delimiter IN VARCHAR )
  RETURN VARCHAR2;
  
  /************************************************************************
  FUNCTION f_undelimit_me
  Summary: Turns a delimited string into an array 
  *************************************************************************/  
  FUNCTION f_undelimit_me
    ( the_string IN VARCHAR2
    , the_delimiter IN VARCHAR2 )
  RETURN owa_util.vc_arr;

  /************************************************************************
  FUNCTION f_none_of_these_chars
  Summary: Removes any occurances of each character in the list_of_chars from
          the_string. I.E. list_of_chars are the bad characters that need to 
          be removed.
  *************************************************************************/
  FUNCTION f_none_of_these_chars
    ( the_string IN VARCHAR2
    , list_of_chars IN VARCHAR2 )
  RETURN VARCHAR2;

  /************************************************************************
  FUNCTION f_count_occurances
  Summary: Counts the number of times that the_char occurs in the_string 
  *************************************************************************/  
  FUNCTION f_count_occurances
    ( the_string IN VARCHAR2
    , the_char IN CHAR )
  RETURN INTEGER; 
  
  /************************************************************************
  FUNCTION f_any_bad_chars
  Summary: Returns Y if any of the characters not contained in the allowed_chars
        exist in the_string 
  *************************************************************************/  
  FUNCTION f_any_bad_chars
    ( the_string IN VARCHAR2
    , allowed_chars IN VARCHAR2 )
  RETURN VARCHAR2;
  
  /************************************************************************
  FUNCTION f_only_these_chars 
  Summary: Removes any characters in the_string that aren't found in the allowed_chars 
  *************************************************************************/  
  FUNCTION f_only_these_chars
    ( the_string IN VARCHAR2
    , allowed_chars IN VARCHAR2 )
  RETURN VARCHAR2;
  
  /************************************************************************
  FUNCTION f_match 
  Summary: Returns Y is the value is found in the array 
           Returns N if the value is NOT found in the array
           Returns E if there was an error 
  *************************************************************************/
  FUNCTION f_match
    ( the_array IN owa_util.vc_arr
    , the_value IN VARCHAR2 )
  RETURN VARCHAR2;
  
END pkg_manip;

/
CREATE OR REPLACE PACKAGE BODY pkg_manip
IS

  FUNCTION isnumeric (infield IN VARCHAR2)                                                                                                                                                                                
  RETURN VARCHAR2                                                                                                                                                                                    
  IS      
    CHECK_FIELD  NUMBER;                                                                                                                                                                                  
  BEGIN
    CHECK_FIELD := TO_NUMBER(INFIELD);                                                                                                                                                                                           
    RETURN 'Y';                                                                                                                                                                                        
  EXCEPTION
    WHEN OTHERS THEN 
      RETURN 'N';                                                                                                                                                                                               
  END isnumeric;

   FUNCTION trimUpper( aString IN VARCHAR2 )
   RETURN VARCHAR2
   IS 
   BEGIN
      RETURN UPPER( TRIM( aString ) );
   EXCEPTION
      WHEN OTHERS THEN
         RETURN NULL;   
   END trimUpper;
   
   FUNCTION timeDate( aDate IN DATE )
   RETURN VARCHAR2
   IS
   BEGIN
    RETURN TO_CHAR( aDate, 'HH:MI am, MM/DD/YYYY' );
   EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
   END timeDate;   
   
   --returns date in MM/DD/YYYY HH:MI:SS Format   
   FUNCTION dateTime( aDate IN DATE )
   RETURN VARCHAR2
   IS
   BEGIN
      RETURN TO_CHAR( aDate, 'MM/DD/YYYY HH:MI:SS' );
   EXCEPTION
      WHEN OTHERS THEN
         RETURN NULL;      
   END dateTime;
   
   FUNCTION dt( aDate IN DATE )
   RETURN VARCHAR2
   IS
   BEGIN
      RETURN TO_CHAR( aDate, 'MM/DD/YYYY' );
   EXCEPTION
      WHEN OTHERS THEN
         RETURN NULL;
   END dt;
   
   FUNCTION numToDollars
      ( aNumber IN NUMBER
      , withDollar IN VARCHAR DEFAULT 'Y' )
   RETURN VARCHAR2
   IS
   BEGIN
      IF trimUpper( withDollar ) = 'Y' THEN
         RETURN '$'||TRIM( TO_CHAR( aNumber, '999,999,999,999,990.99' ) );
      ELSE
         RETURN TRIM( TO_CHAR( aNumber, '999,999,999,999,990.99' ) );
      END IF;
   EXCEPTION
      WHEN OTHERS THEN
         RETURN NULL; 
   END numToDollars;

      FUNCTION f_make_date (value_in IN VARCHAR2)
      RETURN DATE
      IS
         /* Internal, upper-cased version of date string */
         value_int CHAR(100) := UPPER (value_in);
         /* The value returned by the function */
         return_value DATE := NULL;
         /* Transfer SYSDATE to local variable to avoid repetitive calls */
         today DATE := SYSDATE;
      BEGIN
      
         /* 
         || Handle the short-cut logic before checking for specific date formats.
         || Supported short-cuts include:
         ||    EW - end of week
         ||    BW - beginning of week
         ||    EM - end of month
         ||    BM - beginning of month
         ||    TODAY - today's truncated date
         ||    YESTERDAY - yesterday's truncated date
         ||    TOMORROW - tomorrow's truncated date
         ||
         || You might want to add shortcuts for quarters specific to your site.
         */
         IF value_int = 'EW'
         THEN
            /* End of week in this case is Friday of the week */
            return_value := TRUNC (today, 'DAY') + 5;
         ELSIF value_int = 'BW'
         THEN
            /* Beginning of week in this case is Saturday of the week */
            return_value := TRUNC (today, 'DAY') - 1;
         ELSIF value_int = 'BM'
         THEN
            return_value := TRUNC (today, 'MONTH');
         ELSIF value_int = 'EM'
         THEN
            return_value := LAST_DAY (today);
         ELSIF value_int = 'TODAY'
         THEN
            return_value := TRUNC( SYSDATE );
         ELSIF value_int = 'YESTERDAY'
         THEN
            return_value := TRUNC( SYSDATE - 1 );
         ELSIF value_int = 'TOMORROW'
         THEN
            return_value := TRUNC( SYSDATE + 1 );
         ELSIF value_int IS NOT NULL
         THEN
            /* No known short-cut. The user must have entered a date string for
            || conversion. Now attempt to convert the value using a sequence
            || of calls to TO_DATE. If one attempt fails, pass it to the next
            || TO_DATE and format mask within a (very) nested exception section.
            */
            BEGIN return_value := TO_DATE (value_int, 'MM/DD/RR');
            EXCEPTION WHEN OTHERS THEN
            BEGIN return_value := TO_DATE (value_int, 'MM-DD-RR');
            EXCEPTION WHEN OTHERS THEN
            BEGIN return_value := TO_DATE (value_int, 'MM/DD/YYYY');
            EXCEPTION WHEN OTHERS THEN
            BEGIN return_value := TO_DATE (value_int, 'MMDDRR');
            EXCEPTION WHEN OTHERS THEN
            BEGIN    return_value := TO_DATE (value_int, 'DD');
            EXCEPTION WHEN OTHERS THEN
            BEGIN return_value := TO_DATE (value_int, 'MM/DD');
            EXCEPTION WHEN OTHERS THEN
            BEGIN return_value := TO_DATE (value_int, 'DD-MON');
            EXCEPTION WHEN OTHERS THEN
            BEGIN return_value := TO_DATE (value_int, 'DD-MON-RR');
            EXCEPTION WHEN OTHERS THEN
            BEGIN return_value := TO_DATE (value_int, 'DD-MON-YYYY');
            EXCEPTION WHEN OTHERS THEN
            BEGIN return_value := TO_DATE (value_int, 'MON');
            EXCEPTION WHEN OTHERS THEN
            BEGIN return_value := TO_DATE (value_int, 'MON-DD');
            EXCEPTION WHEN OTHERS THEN
            BEGIN return_value := TO_DATE (value_int, 'MON-DD-RR');
            EXCEPTION WHEN OTHERS THEN     /* out */
            BEGIN return_value := TO_DATE (value_int, 'MON-DD-YYYY'); /* out */
            EXCEPTION WHEN OTHERS THEN  /* out */
            BEGIN return_value := TO_DATE (value_int, 'MON-YYYY');  /* out */
            EXCEPTION WHEN OTHERS THEN  /* out */
            BEGIN return_value := TO_DATE (value_int, 'YYYYMMDD');  /* out */
            EXCEPTION WHEN OTHERS THEN return_value := NULL;  /* out */
            END; END; END; END; END; END;END;
            END; END;END; END;END; END;END;END;
         END IF;
         /* Whether NULL or a real date, return the value */
         RETURN (return_value);
      END f_make_date;
      
      FUNCTION f_make_cc_date (value_in IN VARCHAR2)
      RETURN DATE
      IS
         /* Internal, upper-cased version of date string */
         value_int CHAR(100) := UPPER (value_in);
         /* The value returned by the function */
         return_value DATE := NULL;    
      BEGIN
         
         BEGIN return_value := TO_DATE( value_int, 'MMYY' );
         EXCEPTION WHEN OTHERS THEN
         BEGIN return_value := TO_DATE( value_int, 'MM/YY' );
         EXCEPTION WHEN OTHERS THEN return_value := f_make_date( value_int );
         END; END;
         
         RETURN return_value;
      
      END f_make_cc_date;
      
      FUNCTION f_no_cr( value_in IN VARCHAR2 )
      RETURN VARCHAR2
      IS
      BEGIN

           RETURN replace( replace( value_in, chr( 10 ), '' ), chr(13), '' );    
      
      EXCEPTION
         WHEN OTHERS THEN
            RETURN value_in;           
      END f_no_cr;
      
   FUNCTION f_chop( string_in IN VARCHAR2, chop_string IN VARCHAR2 )
   RETURN VARCHAR2
   IS
   BEGIN
      RETURN SUBSTR( string_in, 1, ( INSTR( string_in, chop_string, 1, 1 ) - 1 ) );         
   EXCEPTION
      WHEN OTHERS THEN
         RETURN string_in;
   END f_chop;
   
   FUNCTION f_rchop( string_in IN VARCHAR2, chop_string IN VARCHAR2 )
   RETURN VARCHAR2
   IS
   BEGIN
      RETURN SUBSTR( string_in, ( INSTR( string_in, chop_string, -1 ) + 1 ) );   
   EXCEPTION
      WHEN OTHERS THEN
         RETURN string_in;
   END f_rchop;
   
   FUNCTION f_trail_off
      ( string_in IN VARCHAR2
      , char_to_start_trailing IN INTEGER
      , trailing_chars IN VARCHAR2 )
   RETURN VARCHAR2
   IS
   BEGIN
      IF length( string_in ) <= char_to_start_trailing THEN
         RETURN string_in;
      ELSE
         RETURN SUBSTR( string_in, 1, char_to_start_trailing )||trailing_chars;     
      END IF;
   EXCEPTION
      WHEN OTHERS THEN
         RETURN string_in;
   END f_trail_off;
   
  FUNCTION f_wrap
    ( the_string IN VARCHAR2
    , wrapper IN VARCHAR2 )
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN wrapper||the_string||wrapper;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN the_string;
  END f_wrap;
      
  FUNCTION f_wrap
    ( the_string IN VARCHAR2
    , lhs_wrapper IN VARCHAR2
    , rhs_wrapper IN VARCHAR2 )
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN lhs_wrapper||the_string||rhs_wrapper;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN the_string;
  END f_wrap;
  
  FUNCTION f_strip
    ( the_string IN VARCHAR2
    , the_strip_string IN VARCHAR2 )
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN RTRIM( LTRIM( the_string, the_strip_string ), the_strip_string );
  EXCEPTION
    WHEN OTHERS THEN
      RETURN SQLCODE;
  END f_strip;
  
  FUNCTION f_delimit_me
    ( the_array IN owa_util.vc_arr
    , the_delimiter IN VARCHAR )
  RETURN VARCHAR2
  IS
    first_one BOOLEAN;
    ret_val VARCHAR2( 1024 );
  BEGIN
    first_one := TRUE;
    FOR i IN the_array.FIRST..the_array.LAST LOOP
      IF first_one THEN
        first_one := FALSE;
        ret_val := the_array( i );
      ELSE
        ret_val := ret_val||the_delimiter||the_array( i );
      END IF;
    END LOOP;
    RETURN ret_val;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN SQLERRM;
  END f_delimit_me;
  
  FUNCTION f_undelimit_me
    ( the_string IN VARCHAR2
    , the_delimiter IN VARCHAR2 )
  RETURN owa_util.vc_arr
  IS
    ret_arr owa_util.vc_arr;
    idx NUMBER := 0;
    max_length NUMBER;
    working_string VARCHAR2( 32000 );
  BEGIN

    max_length := LENGTH( the_string );
    working_string := the_string;

    WHILE working_string IS NOT NULL
    LOOP
      idx := idx + 1;
      ret_arr( idx ) := TRIM( SUBSTR( working_string, 1, ( INSTR( working_string, the_delimiter ) - 1 ) ) );
      IF ret_arr( idx ) IS NOT NULL THEN
        working_string := SUBSTR( working_string, ( INSTR( working_string, the_delimiter ) + 1 ) );
      ELSE --LAST TIME THROUGH THERE ARE NO DELIMETERS 
        ret_arr( idx ) := TRIM( working_string );
        working_string := NULL;
      END IF;
      --THIS IS JUST INSURANCE THAT WE WILL GET OUT OF THIS LOOP 
      EXIT WHEN ( idx * 2 ) > max_length;
    END LOOP;
    
    RETURN ret_arr;    
  
  EXCEPTION
    WHEN OTHERS THEN
      idx := idx + 1;
      ret_arr( idx ) := SQLERRM;
      RETURN ret_arr;    
  END f_undelimit_me;
  
  FUNCTION f_none_of_these_chars
    ( the_string IN VARCHAR2
    , list_of_chars IN VARCHAR2 )
  RETURN VARCHAR2
  IS
    the_char CHAR( 1 );
    ret_val VARCHAR2( 32000 );    
  BEGIN
    ret_val := the_string;
    FOR i IN 1..LENGTH( list_of_chars ) LOOP
      the_char := SUBSTR( list_of_chars, i, 1 );
      ret_val := REPLACE( ret_val, the_char );
    END LOOP;
    RETURN ret_val;  
  EXCEPTION
    WHEN OTHERS THEN
      RETURN SQLCODE;
  END f_none_of_these_chars;
  
  FUNCTION f_count_occurances
    ( the_string IN VARCHAR2
    , the_char IN CHAR )
  RETURN INTEGER
  IS
    loop_char CHAR( 1 );
    ret_val INTEGER := 0;
  BEGIN
    FOR i IN 1..LENGTH( the_string ) LOOP
      loop_char := SUBSTR( the_string, i, 1 );
      IF loop_char = the_char THEN
        ret_val := ret_val + 1;
      END IF;    
    END LOOP;
    RETURN ret_val;
  EXCEPTION
    WHEN OTHERS THEN
       RETURN -1;
  END f_count_occurances;
  
  FUNCTION f_any_bad_chars
    ( the_string IN VARCHAR2
    , allowed_chars IN VARCHAR2 )
  RETURN VARCHAR2
  IS
    loop_char CHAR( 1 );
    ret_val VARCHAR2( 10 );
  BEGIN
  
    ret_val := 'N';
    FOR i IN 1..LENGTH( the_string ) LOOP
      IF ret_val = 'Y' THEN
        EXIT;
      ELSE
        loop_char := SUBSTR( the_string, i , 1 );
        IF allowed_chars NOT LIKE '%'||loop_char||'%' THEN
          ret_val := 'Y';
        END IF;
      END IF;
    END LOOP;
    
    RETURN ret_val;
    
  EXCEPTION
    WHEN OTHERS THEN
      RETURN SQLCODE;      
  END f_any_bad_chars;
  
  FUNCTION f_only_these_chars
    ( the_string IN VARCHAR2
    , allowed_chars IN VARCHAR2 )
  RETURN VARCHAR2
  IS
    the_char CHAR( 1 );
    ret_val VARCHAR2( 32000 );    
  BEGIN
    ret_val := the_string;
    FOR i IN 1..LENGTH( the_string ) LOOP
      the_char := SUBSTR( the_string, i, 1 );
      IF allowed_chars NOT LIKE '%'||the_char||'%' THEN
        ret_val := REPLACE( ret_val, the_char );        
      END IF;
    END LOOP;
    RETURN ret_val;    
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;  
  END f_only_these_chars;
  
  FUNCTION f_match
    ( the_array IN owa_util.vc_arr
    , the_value IN VARCHAR2 )
  RETURN VARCHAR2
  IS
    match_found BOOLEAN := FALSE;
  BEGIN
  
    FOR i IN the_array.FIRST..the_array.LAST LOOP
      IF the_array( i ) = the_value THEN
        match_found := TRUE;
      END IF;
    END LOOP;
    
    IF match_found THEN
      RETURN 'Y';
    ELSE
      RETURN 'N';
    END IF;
  
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE( SUBSTR( SQLERRM, 1, 255 ) );
      RETURN 'E';  
  END f_match;
  
END pkg_manip;

/
